package com.sisucc.contractmanage.dao.staffInfo;

import com.sisucc.contractmanage.bean.staffInfo.StaffInfoBean;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author linyuqi
 */
@Repository
public interface StaffInfoRepository extends JpaRepository<StaffInfoBean, String>,
        JpaSpecificationExecutor<StaffInfoBean> {

    /**
     * 查询某年十二个月的合同数量
     * @param yearInit init
     * @param yearEnd end
     * @return
     */
    @Query(value = "select date_format(create_time, '%Y-%m') date, count(contract_id) num from tb_contract_info where create_time >= ?1 and create_time <= ?2 group by date_format(create_time, '%Y-%m')", nativeQuery = true)
    List<Map<String, String>> statisticReportByYear(String yearInit, String yearEnd);

    /**
     * 按合同的分类查询的合同数量
     * @return
     */
    @Query(value = "select contract_type_name name, count(contract_id) num from tb_contract_info group by contract_type_name", nativeQuery = true)
    List<Map<String, String>> statisticReportByType();

    /**
     * 按部门查询合同的数量的合同数量
     * @return
     */
    @Query(value = "select depart_name name, count(contract_id) num from tb_contract_info group by depart_name", nativeQuery = true)
    List<Map<String, String>> statisticReportByDepart();
}
